﻿using Microsoft.Office.Tools;
using Microsoft.Office.Tools.Ribbon;
using System;
using System.Collections.Generic;
using System.Diagnostics; //如果是VSTO项目，需要使用这个进行Debug输出
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace ExcelAddIn1
{
    public partial class Ribbon1
    {
        Excel.Application app;
        UserControl1 uc1;
        CustomTaskPane ctp1;
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
            //Ribbon加载时的初始化（可留空）
            Debug.WriteLine("Ribbon组件加载了");
            app = Globals.ThisAddIn.Application;//当组件加载时，获取Excel Application对象
            Debug.WriteLine(app.Version);//打印当前app版本信息
        }

        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Application app = Globals.ThisAddIn.Application;
            bool oldScreenUpdating = app.ScreenUpdating;
            app.ScreenUpdating = false;

            System.Diagnostics.Debug.WriteLine("成功关闭屏幕更新");

            try
            {
                // 遍历所有工作表
                foreach (Excel.Worksheet ws in app.Worksheets)
                {
                    // 使用字符串地址存储合并区域，避免COM对象问题
                    List<string> mergeAreaAddresses = new List<string>();
                    Excel.Range usedRange = null;//起始默认range为null类型，

                    try
                    {
                        usedRange = ws.UsedRange;
                        if (usedRange != null)
                        {
                            // 遍历UsedRange中的每个单元格
                            foreach (Excel.Range rng in usedRange.Cells)
                            {
                                try
                                {
                                    System.Diagnostics.Debug.WriteLine(rng.Address);

                                    // 检查是否为合并单元格且未重复添加
                                    if (rng.MergeCells)
                                    {
                                        Excel.Range mergeArea = rng.MergeArea;
                                        string address = mergeArea.Address;

                                        if (!mergeAreaAddresses.Contains(address))
                                        {
                                            mergeAreaAddresses.Add(address);
                                        }

                                        // 立即释放mergeArea
                                        Marshal.ReleaseComObject(mergeArea);
                                    }

                                    // 释放rng
                                    Marshal.ReleaseComObject(rng);
                                }
                                catch (Exception cellEx)
                                {
                                    System.Diagnostics.Debug.WriteLine($"处理单元格时出错: {cellEx.Message}");
                                    // 继续处理下一个单元格
                                }
                            }

                            // 复制并粘贴为数值
                            usedRange.Copy();
                            usedRange.PasteSpecial(Excel.XlPasteType.xlPasteValues);

                            // ✅ 正确清除剪贴板
                            app.CutCopyMode = Excel.XlCutCopyMode.xlCopy;

                            // ✅ 使用地址恢复合并区域
                            foreach (string address in mergeAreaAddresses)
                            {
                                try
                                {
                                    Excel.Range areaToMerge = ws.Range[address];
                                    areaToMerge.Merge();
                                    Marshal.ReleaseComObject(areaToMerge);
                                }
                                catch (Exception mergeEx)
                                {
                                    System.Diagnostics.Debug.WriteLine($"恢复合并区域失败 {address}: {mergeEx.Message}");
                                }
                            }
                        }
                    }
                    finally
                    {
                        // 释放usedRange
                        if (usedRange != null)
                            Marshal.ReleaseComObject(usedRange);

                        // 释放worksheet
                        Marshal.ReleaseComObject(ws);
                    }
                }
                //复原原来的屏幕更新设置
                app.ScreenUpdating = oldScreenUpdating;
                MessageBox.Show("所有表格已复制粘贴为数值并保留合并单元格。", "操作完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                app.ScreenUpdating = oldScreenUpdating;
                MessageBox.Show($"执行过程中发生错误: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 强制垃圾回收
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
        }

        private static bool IsRangeAlreadyAdded(Excel.Range cell, List<string> mergeAreaAddresses)
        {
            if (!cell.MergeCells) return false;

            Excel.Range mergeArea = cell.MergeArea;
            string address = mergeArea.Address;
            Marshal.ReleaseComObject(mergeArea);

            return mergeAreaAddresses.Contains(address);
        }

        private static bool IsRangeAlreadyAdded(Excel.Range cell, List<Excel.Range> mergeAreas)
        {
            // 检查区域是否已经添加到列表中
            foreach (Excel.Range area in mergeAreas)
            {
                // 比较地址以确定是否已添加
                if (area.MergeArea != null && area.MergeArea.Address == cell.MergeArea.Address)
                {
                    return true;
                }
                else if (area.Address == cell.Address)
                {
                    return true;
                }
            }
            return false;
        }

        private void editBox1_TextChanged(object sender, RibbonControlEventArgs e)
        {
            //Debug.WriteLine("编辑框文本改变了："+this.editBox1.Text);
            this.uc1.textBox1.Text = this.editBox1.Text;
        }

        private void button2_Click(object sender, RibbonControlEventArgs e)
        {
            foreach (Excel.Workbooks workbook in app.Workbooks)
            {
                Debug.WriteLine(workbook);
            }
        }

        private void group1_DialogLauncherClick(object sender, RibbonControlEventArgs e)
        {
            Debug.WriteLine("DialogLauncher被点击了");
            MessageBox.Show("这是消息框");
        }

        private void button3_Click(object sender, RibbonControlEventArgs e)
        {
            uc1 = new UserControl1();//这里创建了一个用户控件
            //Console.WriteLine("插件启动了");
            ctp1 = Globals.ThisAddIn.CustomTaskPanes.Add(uc1, "VSTO");
            Debug.WriteLine(ctp1.Control.Name);
            ctp1.Visible = true;
            ctp1.DockPosition = Office.MsoCTPDockPosition.msoCTPDockPositionRight;
            ctp1.Width = 300;
        }

        private void button4_Click(object sender, RibbonControlEventArgs e)
        {
            //Debug.WriteLine("输出一下内容");
            Debug.WriteLine(this.editBox1.Text);
        }

        private void button5_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                //获取当前活动工作薄
                Excel.Workbook activeWorkbook = Common.ExcelApp.ActiveWorkbook;
                if (activeWorkbook == null)
                {
                    Debug.WriteLine("无法获取到活动工作薄");
                }
                Debug.WriteLine(activeWorkbook);
                if (activeWorkbook == null)
                {
                    System.Windows.Forms.MessageBox.Show("没有打开的工作薄！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                    return;
                }

                //获取当前活动工作表
                Excel.Worksheet activeSheet = activeWorkbook.ActiveSheet as Excel.Worksheet;

                //获取A1单元格的值
                Excel.Range rangeA1 = activeSheet.Range["A1"];
                object a1Value = rangeA1.Value2;

                string fileName = a1Value != null ? a1Value.ToString().Trim() : "未命名文件";

                //去除文件名中的非法字符
                foreach (char c in System.IO.Path.GetInvalidFileNameChars())
                {
                    fileName = fileName.Replace(c, '-');
                }

                if (string.IsNullOrEmpty(fileName))
                {
                    fileName = "未命名文件";
                }

                //拼接保存路径(比如保存到D:\Temp\
                string folderPath = @"D:\Temp\";
                if (!System.IO.Directory.Exists(folderPath))
                {
                    System.IO.Directory.CreateDirectory(folderPath);
                }

                string fullFilePath = System.IO.Path.Combine(folderPath, fileName + ".xlsx");

                // 另存为新文件
                activeWorkbook.SaveAs(fullFilePath);

                // 提示用户
                System.Windows.Forms.MessageBox.Show($"文件已成功保存为：\n{fullFilePath}", "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (System.Exception ex) {
                System.Windows.Forms.MessageBox.Show("保存失败: " + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

         }

        private void button6_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Application app = Globals.ThisAddIn.Application;
            Excel.Workbook wb = app.ActiveWorkbook;
            Excel.Worksheet activeSheet = app.ActiveSheet;
            if (wb != null && activeSheet != null) {
                Debug.WriteLine(wb.Path);
                Debug.WriteLine(wb.Title);


                //将该活动工作薄中的所有工作表，转换为list类型数据
                var sheets = wb.Worksheets.Cast<Excel.Worksheet>().ToList();
                for (int i = sheets.Count - 1; i >= 0; i--)
                {
                    if (!object.ReferenceEquals(sheets[i], activeSheet))
                    {
                        Debug.WriteLine(sheets[i].Name+"表即将被删除");
                        sheets[i].Delete();
                    }
                } 
            }
        }
    }
}
